<?php



function updateAppRates($listobject, $projectid, $scenarioid, $subsheds, $thisyear, $landuses, $apprateinfo, $debug) {

   # queries for the lrseg landuses in the given
   # assemble input variables into conditions
   if (count($subsheds) > 0) {
      $sslist = "'" . join("','", $subsheds) . "'";
      $subshedcond = " lrseg in ($sslist) ";
   } else {
      $subshedcond = ' 1 = 1 ';
   }

   if (strlen($thisyear) > 0) {
      $yrcond = " thisyear in ($thisyear) ";
      $ayrcond = " a.thisyear in ($thisyear) ";
      $byrcond = " b.thisyear in ($thisyear) ";
   } else {
      $yrcond = ' 1 = 1 ';
      $ayrcond = ' 1 = 1 ';
      $byrcond = ' 1 = 1 ';
   }

   if (count($landuses) > 0) {
      $lulist = "'" . join("','", $landuses) . "'";
      $lucond = " luname in ($lulist) ";
      $alucond = " a.luname in ($lulist) ";
      $blucond = " b.luname in ($lulist) ";
   } else {
      $lucond = ' 1 = 1 ';
      $alucond = ' 1 = 1 ';
      $blucond = ' 1 = 1 ';
   }

   # get submitted values
   $limconstit = $apprateinfo['limconstit'];
   $nrate = $apprateinfo['nrate'];
   $prate = $apprateinfo['prate'];
   $maxnrate = $apprateinfo['maxnrate'];
   $maxprate = $apprateinfo['maxprate'];
   $maxyieldtarget = $apprateinfo['maxyieldtarget'];
   $optyieldtarget = $apprateinfo['optyieldtarget'];

   switch($optyieldtarget) {
      case 1:
      $noptcol = 'mean_needn';
      $poptcol = 'mean_needp';
      break;
      case 2:
      $noptcol = 'mean_uptn';
      $poptcol = 'mean_uptp';
      break;
      case 3:
      $noptcol = 'targ_needn';
      $poptcol = 'targ_needp';
      break;
      case 4:
      $noptcol = 'targ_uptn';
      $poptcol = 'targ_uptp';
      break;
      case 5:
      $noptcol = 'high_needn';
      $poptcol = 'high_needp';
      break;
      case 6:
      $noptcol = 'high_uptn';
      $poptcol = 'high_uptp';
      break;
      default:
      $noptcol = 'high_needn';
      $poptcol = 'high_needp';
      break;
   }

   switch($maxyieldtarget) {
      case 1:
      $nmaxcol = 'mean_needn';
      $pmaxcol = 'mean_needp';
      break;
      case 2:
      $nmaxcol = 'mean_uptn';
      $pmaxcol = 'mean_uptp';
      break;
      case 3:
      $nmaxcol = 'targ_needn';
      $pmaxcol = 'targ_needp';
      break;
      case 4:
      $nmaxcol = 'targ_uptn';
      $pmaxcol = 'targ_uptp';
      break;
      case 5:
      $nmaxcol = 'high_needn';
      $pmaxcol = 'high_needp';
      break;
      case 6:
      $nmaxcol = 'high_uptn';
      $pmaxcol = 'high_uptp';
      break;
      default:
      $nmaxcol = 'high_needn';
      $pmaxcol = 'high_needp';
      break;
   }


   # insert new bmp results

   $listobject->querystring = " update inputyields set nm_planbase = $limconstit,  ";
   $listobject->querystring .= "  nrate = $nrate, prate = $prate, ";
   $listobject->querystring .= "  optn = $nrate * $noptcol, optp = $prate * $poptcol,  ";
   $listobject->querystring .= "  maxnrate = $maxnrate, maxprate = $maxprate, ";
   $listobject->querystring .= "  maxn = $maxnrate * $nmaxcol, maxp = $prate * $pmaxcol,  ";
   $listobject->querystring .= "  maxyieldtarget = $maxyieldtarget, optyieldtarget = $optyieldtarget  ";
   $listobject->querystring .= " where scenarioid = $scenarioid  ";
   $listobject->querystring .= "    and subshedid in (select subshedid from scen_lrsegs where  ";
   $listobject->querystring .= "        scenarioid = $scenarioid  ";
   $listobject->querystring .= "        and $subshedcond  ";
   $listobject->querystring .= "        and $yrcond  ";
   $listobject->querystring .= "        group by subshedid ";
   $listobject->querystring .= "    ) ";
   $listobject->querystring .= "    and $yrcond ";
   $listobject->querystring .= "    and $lucond ";
   if ($debug) { print("<br>$listobject->querystring ;<br>"); }
   $listobject->performQuery();

}


function calculateLUNeedFromCrops($listobject, $projectid, $scenarioid, $subsheds, $thisyear, $landuses, $apprateinfo, $debug) {

   # clacluates the aggregate, crop area weighted crop uptake, need, etc. from the crops in the crop table

   # queries for the lrseg landuses in the given
   # assemble input variables into conditions
   if (count($subsheds) > 0) {
      $sslist = "'" . join("','", $subsheds) . "'";
      $subshedcond = " lrseg in ($sslist) ";
   } else {
      $subshedcond = ' 1 = 1 ';
   }

   if (strlen($thisyear) > 0) {
      $yrcond = " thisyear in ($thisyear) ";
      $ayrcond = " a.thisyear in ($thisyear) ";
      $byrcond = " b.thisyear in ($thisyear) ";
   } else {
      $yrcond = ' 1 = 1 ';
      $ayrcond = ' 1 = 1 ';
      $byrcond = ' 1 = 1 ';
   }

   if (count($landuses) > 0) {
      $lulist = "'" . join("','", $landuses) . "'";
      $lucond = " luname in ($lulist) ";
      $alucond = " a.luname in ($lulist) ";
      $blucond = " b.luname in ($lulist) ";
   } else {
      $lucond = ' 1 = 1 ';
      $alucond = ' 1 = 1 ';
      $blucond = ' 1 = 1 ';
   }


   # delete old crop need values

   $listobject->querystring = " delete from inputyields ";
   $listobject->querystring .= " where scenarioid = $scenarioid  ";
   $listobject->querystring .= "    and subshedid in (select subshedid from scen_lrsegs where  ";
   $listobject->querystring .= "        scenarioid = $scenarioid  ";
   $listobject->querystring .= "        and $subshedcond  ";
   $listobject->querystring .= "        and $yrcond  ";
   $listobject->querystring .= "        group by subshedid ";
   $listobject->querystring .= "    ) ";
   $listobject->querystring .= "    and $yrcond ";
   $listobject->querystring .= "    and $lucond ";
   if ($debug) { print("<br>$listobject->querystring ;<br>"); }
   $listobject->performQuery();


delete from inputyields ;

   $listobject->querystring = "  insert into inputyields (projectid, scenarioid, subshedid, ";
   $listobject->querystring .= "    thisyear, luname, maxn, maxp, optn, optp, ";
   $listobject->querystring .= "    total_acres, legume_n, uptake_n, uptake_p, total_n, ";
   $listobject->querystring .= "    total_p, nrate, prate,  maxnrate, maxprate, mean_uptn, ";
   $listobject->querystring .= "    mean_uptp, high_uptn, high_uptp, targ_uptn, targ_uptp, ";
   $listobject->querystring .= "    mean_needn, mean_needp, high_needn, high_needp, targ_needn, ";
   $listobject->querystring .= "    targ_needp, n_urratio, p_urratio, n_fix )";
   $listobject->querystring .= " select :pjid, :scid, stcofips as subshedid, thisyear, luname,";
   $listobject->querystring .= "    sum((1.0 - n_fix) * crop_yield * cropacres * n_uptake)";
   $listobject->querystring .= "       /sum(cropacres) as maxn,";
   $listobject->querystring .= "    sum(crop_yield * cropacres * p_uptake)/sum(cropacres) as maxp,";
   $listobject->querystring .= "    sum((1.0 - n_fix) * crop_yield * cropacres * n_uptake)/sum(cropacres) as optn,
   sum(crop_yield * cropacres * p_uptake)/sum(cropacres) as optp,
   sum(cropacres) as total_acres,
   sum(n_fix * crop_yield * cropacres * n_uptake)/sum(cropacres) as legume_n,
   sum(crop_yield * cropacres * n_uptake)/sum(cropacres) as uptake_n,
   sum(crop_yield * cropacres * p_uptake)/sum(cropacres) as uptake_p ,
   sum((1.0 - n_fix) * crop_yield * cropacres * n_uptake) as total_n,
   sum(crop_yield * cropacres * p_uptake) as total_p,
   1.0 as nrate, 1.0 as prate,
   1.0 as maxnrate, 1.0 as maxprate,
   sum(avg_yld * cropacres * n_uptake)/sum(cropacres) as mean_uptn,
   sum(avg_yld * cropacres * p_uptake)/sum(cropacres) as mean_uptp,
   sum(high_yld * cropacres * n_uptake)/sum(cropacres) as high_uptn,
   sum(high_yld * cropacres * p_uptake)/sum(cropacres) as high_uptp,
   sum(targ_yld * cropacres * n_uptake)/sum(cropacres) as targ_uptn,
   sum(targ_yld * cropacres * p_uptake)/sum(cropacres) as targ_uptp,
   sum((1.0 - n_fix) * avg_yld * cropacres * n_uptake)/sum(cropacres) as mean_needn,
   sum(avg_yld * cropacres * p_uptake)/sum(cropacres) as mean_needp,
   sum((1.0 - n_fix) * high_yld * cropacres * n_uptake)/sum(cropacres) as high_needn,
   sum(high_yld * cropacres * p_uptake)/sum(cropacres) as high_needp,
   sum((1.0 - n_fix) * targ_yld * cropacres * n_uptake)/sum(cropacres) as targ_needn,
   sum(targ_yld * cropacres * p_uptake)/sum(cropacres) as targ_needp,
   1.5, 1.1, sum(n_fix * cropacres)/sum(cropacres) as n_fix
from cropyield
where cropacres > 0
--and luname not in ('clo', 'chi')
--and thisyear = 1987
group by stcofips, luname, thisyear
--order by maxn desc;
order by thisyear, stcofips, luname;


--select * from inputyields where subshedid = 10001 and thisyear = 1997 and luname = 'hwm';

-- calculate the effects of double-cropping on corn-grain-soy rotations
-- only certain areas can have double-cropping
-- see the file double_crops.sql for the determination of acres/counties
-- where double-cropping is occuring
-- see double_crops.sql for creation of double crop table
update inputyields set
   uptake_n = a.uptake_n / (1.0 - b.double_crop_pct ),
   uptake_p = a.uptake_p / (1.0 - b.double_crop_pct ),
   maxn = a.maxn / (1.0 - b.double_crop_pct ),
   maxp = a.maxp / (1.0 - b.double_crop_pct ),
   optn = a.optn / (1.0 - b.double_crop_pct ),
   optp = a.optp / (1.0 - b.double_crop_pct ),
   mean_uptn = a.mean_uptn / (1.0 - b.double_crop_pct ),
   mean_uptp = a.mean_uptp / (1.0 - b.double_crop_pct ),
   targ_uptn = a.targ_uptn / (1.0 - b.double_crop_pct ),
   targ_uptp = a.targ_uptp / (1.0 - b.double_crop_pct ),
   high_uptn = a.high_uptn / (1.0 - b.double_crop_pct ),
   high_uptp = a.high_uptp / (1.0 - b.double_crop_pct ),
   mean_needn = a.mean_needn / (1.0 - b.double_crop_pct ),
   mean_needp = a.mean_needp / (1.0 - b.double_crop_pct ),
   targ_needn = a.targ_needn / (1.0 - b.double_crop_pct ),
   targ_needp = a.targ_needp / (1.0 - b.double_crop_pct ),
   high_needn = a.high_needn / (1.0 - b.double_crop_pct ),
   high_needp = a.high_needp / (1.0 - b.double_crop_pct ),
   dc_pct = b.double_crop_pct
from inputyields as a, agc_double_crop as b
where inputyields.subshedid = b.stcofips
and inputyields.subshedid = a.subshedid
and inputyields.thisyear = b.thisyear
and inputyields.thisyear = a.thisyear
and inputyields.luname in ('hwm', 'lwm', 'nhi', 'nlo')
and a.luname in ('hwm', 'lwm', 'nhi', 'nlo')
and b.double_crop_pct > 0
and inputyields.projectid = :pjid
and inputyields.scenarioid = :scid
and a.projectid = :pjid
and a.scenarioid = :scid;
   # insert new crop need values

   $listobject->querystring = " update inputyields set nm_planbase = $limconstit,  ";
   $listobject->querystring .= "  nrate = $nrate, prate = $prate, ";
   $listobject->querystring .= "  optn = $nrate * $noptcol, optp = $prate * $poptcol,  ";
   $listobject->querystring .= "  maxnrate = $maxnrate, maxprate = $maxprate, ";
   $listobject->querystring .= "  maxn = $maxnrate * $nmaxcol, maxp = $prate * $pmaxcol,  ";
   $listobject->querystring .= "  maxyieldtarget = $maxyieldtarget, optyieldtarget = $optyieldtarget  ";
   $listobject->querystring .= " where scenarioid = $scenarioid  ";
   $listobject->querystring .= "    and subshedid in (select subshedid from scen_lrsegs where  ";
   $listobject->querystring .= "        scenarioid = $scenarioid  ";
   $listobject->querystring .= "        and $subshedcond  ";
   $listobject->querystring .= "        and $yrcond  ";
   $listobject->querystring .= "        group by subshedid ";
   $listobject->querystring .= "    ) ";
   $listobject->querystring .= "    and $yrcond ";
   $listobject->querystring .= "    and $lucond ";
   if ($debug) { print("<br>$listobject->querystring ;<br>"); }
   $listobject->performQuery();

}

?>